package com.cindata

import grails.converters.JSON
import grails.gorm.transactions.Transactional
import org.springframework.security.access.annotation.Secured

import java.text.SimpleDateFormat
import com.cindata.dimension.*

@Secured(['isAuthenticated()'])
@Transactional
class MainController
{
    def springSecurityService
    def dataSource

    static allowedMethods = [save: "POST", update: "PUT", delete: "DELETE"]

    def welcome()
    {
        def user = User.findById(springSecurityService.principal.id)
        if (UserRole.findByUserAndRole(user, Role.findByAuthority("ROLE_YCLOAN")))
        {
            redirect action: "index"
        } 
        else if (UserRole.findByUserAndRole(user, Role.findByAuthority("ROLE_KMUSER")))
        {
            redirect controller:'newMap', action: "heatmap"
        }
        else
        {
            def url = user?.profile?.url
            if(url)
            {
                redirect(url: url)
            }
            else
            {
                redirect(action: "heatIndex")
            }
        }
    }

    def index()
    {
        
    }

    def heatIndex()
    {
        
    }

    def heatMapIndex()
    {}

    // 预警代办数量
    def getPendingCounts()
    {
        def user = User.findById(springSecurityService.principal.id)

        // 价值波动数量
        def priceFluctuationsRatioSql = " select count(r.id) from RiskWarning r where r.type.code = '1' and r.isWarning = '1' and r.account.id in " + Account.FilterAccountSql(user)
        def priceFluctuationsRatioCount = RiskWarning.executeQuery(priceFluctuationsRatioSql)
        // 押品效期到期数量
        def effectiveMaturitySql = " select count(r.id) from RiskWarning r where r.type.code = '14' and r.isWarning = '1' and r.account.id in " + Account.FilterAccountSql(user)
        def effectiveMaturityCount = RiskWarning.executeQuery(effectiveMaturitySql)
        // 集中度预警数量数量
        def concentrationSql = " select count(r.id) from RiskWarning r where r.type.code = '7' and r.isWarning = '1' and r.account.id in " + Account.FilterAccountSql(user)
        def concentrationCount = RiskWarning.executeQuery(concentrationSql)
        // 楼盘风险预警数量
        def buildingScoreSql = " select count(r.id) from RiskWarning r where r.type.code = '15' and r.isWarning = '1' and r.account.id in " + Account.FilterAccountSql(user)
        def buildingScoreCount = RiskWarning.executeQuery(buildingScoreSql)
        // 覆盖率预警数量
        def collateralCoverageRateSql = " select count(r.id) from RiskWarning r where r.type.code = '10' and r.isWarning = '1' and r.account.id in " + Account.FilterAccountSql(user)
        def collateralCoverageRateCount = RiskWarning.executeQuery(collateralCoverageRateSql)

        // 反欺诈
        def antiFraudSql =  "select count(e.id) from Mortgage e where e.warningStatus.code in ('2', '3') and e.account.id in " + Account.FilterAccountSql(user)
        def antiFraudCount = Mortgage.executeQuery(antiFraudSql)

        // 待办
        def roleSql = ""
        def roleList = UserRole.findAllByUser(user)*.role
        if (roleList) 
        {
            String roleIdList = roleList*.id?.toString()
            roleSql += " and role.id in (" + roleIdList?.substring(1, roleIdList.lastIndexOf("]")) + ")"
        }

        // 借款人风控预警
        def contactWarningCount = Contact.countByCreditScoreLessThanEqualsAndAccount(CreditScoreRiskDimension.findByLabel("高风险")?.maxValue, user?.account)

        render([contactWarningCount: contactWarningCount, priceFluctuationsRatioCount: priceFluctuationsRatioCount[0], antiFraudCount: antiFraudCount[0], effectiveMaturityCount: effectiveMaturityCount[0], concentrationCount: concentrationCount[0], buildingScoreCount: buildingScoreCount[0], collateralCoverageRateCount: collateralCoverageRateCount[0]] as JSON)
    }

    def index1()
    {
        //        def id = springSecurityService.principal.id
        //        session.setAttribute('user', User.findById(id))
    }

    def indexGis()
    {
        //        def id = springSecurityService.principal.id
        //        session.setAttribute('user', User.findById(id))
    }

    //统计数据
    def statistics()
    {
        def province = params["province"]
        def city = params["city"]
        def zoneNumber = params["zoneNumber"]
        def level = params["level"]
        def noBeijingSql = "  "

        //当月第一天
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        Calendar c = Calendar.getInstance();
        c.add(Calendar.MONTH, 0);
        c.set(Calendar.DAY_OF_MONTH, 1); //设置为1号,当前日期既为本月第一天
        String first = format.format(c.getTime());
        //当月最后一天
        Calendar ca = Calendar.getInstance();
        ca.set(Calendar.DAY_OF_MONTH, ca.getActualMaximum(Calendar.DAY_OF_MONTH));
        String last = format.format(ca.getTime());
        def result
        def monthResult
        def sql = "select new map(sum(e.loansAmount) as loansAmount," + "sum(e.loansBalance) as loansBalance, sum(e" + ".buildArea) as buildArea,count(e.id) as estateCount,sum(e.totalPrice) as " + "loansEvaluatePrice) from Estate  e "
        def monthSql = "select new map(count(e.id) as  count)from Estate e where 1=1 "
        if ((city == '-1' || zoneNumber == '-1') && level == '1') //全国
        {
            sql += " where 1=1 " + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
            monthSql += " and to_char(e.createdDate,'YYYY-MM-DD') >= '${first}' and to_char(e.createdDate,'YYYY-MM-DD') " + "<='${last}'" + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
            result = Estate.executeQuery(sql)
            monthResult = Estate.executeQuery(monthSql).asList()
        }
        else if (city != '-1' && city && level == '2')
        {
            sql += " where  e.province.name = '${city}'" + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
            monthSql += " and e.province.name = '${city}' and to_char(e.createdDate,'YYYY-MM-DD') >= '${first}' and to_char(e.createdDate,'YYYY-MM-DD') <='${last}'" + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
            result = Estate.executeQuery(sql)
            monthResult = Estate.executeQuery(monthSql).asList()
        }
        else if (zoneNumber != '-1' && zoneNumber && level == '2')
        {
            sql += " where  e.province.zoneNumber = '${zoneNumber}'" + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
            monthSql += " and e.province.zoneNumber = '${zoneNumber}' and to_char(e.createdDate,'YYYY-MM-DD') >= " + "'${first}' and to_char(e.createdDate,'YYYY-MM-DD') <='${last}'" + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
            result = Estate.executeQuery(sql)
            monthResult = Estate.executeQuery(monthSql).asList()
        }
        else if (city != '-1' && city && level == '3')
        {
            sql += " where  e.city.name = '${city}'" + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
            monthSql += " and e.city.name = '${city}' and to_char(e.createdDate,'YYYY-MM-DD') >= '${first}' and" + " to_char(e.createdDate,'YYYY-MM-DD') <='${last}'" + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
            result = Estate.executeQuery(sql)
            monthResult = Estate.executeQuery(monthSql).asList()
        }
        else if (zoneNumber != '-1' && zoneNumber && level == '3')
        {
            sql += " where  e.city.zoneNumber = '${zoneNumber}'" + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
            monthSql += " and e.city.zoneNumber = '${zoneNumber}' and to_char(e.createdDate,'YYYY-MM-DD') >= " + "'${first}'and to_char(e.createdDate,'YYYY-MM-DD') <='${last}'" + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id))
            result = Estate.executeQuery(sql)
            monthResult = Estate.executeQuery(monthSql).asList()
        }
        else
        {
            render([dataResult: null] as JSON)
        }

        result[0].monthResult = monthResult[0].count
        render([dataResult: result] as JSON)
    }

    //地图数据
    def mapData()
    {
        def mapData = new HashMap()
        def city = params["city"]
        def type = params["type"]
        def noBeijingSql = " and e.city.id <> 2 "
        //        if(city == '-1'){
        //获取用户开通的城市
        //            def cityList = this.getCityListByUser()
        if (type == '1')
        {
            //每个城市的押品数量
            def sql1 = "select new map(count(e.id) as value,e.city.name as cityName)" + "from Estate e where 1=1 " + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " group by e.city.name"
            def cityResults = Estate.executeQuery(sql1).asList()
            //每个省的押品
            def sql2 = "select new map(count(e.id) as value,e.province.name as cityName " + ")  from Estate e  where e.province.name not in (:provinceList) " + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " group by e.province.name"
            def provinceResults = Estate.executeQuery(sql2, ["provinceList": ['北京市', '重庆市', '上海市', '天津市']]).asList()
            //每个行政区的
            def sql3 = "select new map(count(e.id) as value,e.district.name as cityName)from Estate e where 1=1 " + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  group by e" + ".district.name "
            def districtResults = Estate.executeQuery(sql3).asList()
            provinceResults.addAll(cityResults)
            provinceResults.addAll(districtResults)
            mapData = provinceResults
        }
        if (type == '2')
        {
            //每个城市的贷款余额
            def sql1 = "select new map(sum(e.loansBalance) as value,e.city.name as cityName )from Estate e where 1=1  " + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  group by e.city.name"
            def cityResults = Estate.executeQuery(sql1).asList()
            //省
            def sql2 = "select new map(sum(e.loansBalance) as value,e.province.name as cityName )from Estate e " + " where e.province.name not in (:provinceList) " + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " group by e.province.name"
            def provinceResults = Estate.executeQuery(sql2, ["provinceList": ['北京市', '重庆市', '上海市', '天津市']]).asList()
            //行政区
            def sql3 = "select new map(sum(e.loansBalance) as value,e.district.name as cityName)from " + "Estate e where 1=1  " + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  group by e.district.name"
            def districtResults = Estate.executeQuery(sql3).asList()
            provinceResults.addAll(cityResults)
            provinceResults.addAll(districtResults)
            mapData = provinceResults
        }
        if (type == '3')
        {
            //省
            def sql1 = "select new map(sum(e.loansBalance) as loansBalance, sum(e.totalPrice)  as " + "loansEvaluatePrice ,e.province.name as  cityName )from Estate e " + " where e.province.name not in (:provinceList) " + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " group by e.province.name"
            def provinceResults = Estate.executeQuery(sql1, ["provinceList": ['北京市', '重庆市', '上海市', '天津市']]).asList()

            //每个城市的 动态抵押率=贷款余额/押品价值
            def sql2 = "select new map(sum(e.loansBalance) as loansBalance, sum(e.totalPrice)  as " + "loansEvaluatePrice ,e.city.name as  cityName )from Estate e where 1=1  " + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " group by e.city.name "
            def cityResults = Estate.executeQuery(sql2).asList()
            //区
            //每个城市的 动态抵押率=贷款余额/押品价值
            def sql3 = "select new map(sum(e.loansBalance) as loansBalance, sum(e.totalPrice)  as " + "loansEvaluatePrice ,e.district.name as  cityName )from Estate e where 1=1  " + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  group by e.district.name"
            def districtResults = Estate.executeQuery(sql3).asList()
            provinceResults.addAll(cityResults)
            provinceResults.addAll(districtResults)
            provinceResults.each {
                if (it.loansBalance && it.loansEvaluatePrice)
                {
                    def dynamicMortgageRate = it.loansBalance / it.loansEvaluatePrice
                    it.value = dynamicMortgageRate.setScale(4, BigDecimal.ROUND_HALF_UP) * 100
                }
            }

            mapData = provinceResults
        }


        //        }else if(city!= '-1'&&city){
        //            if(type=='1'){
        //                //每个行政区的押品数量
        //                def sql1 = "select new map(count(e.id) as estateCount,e.district.name as cityName,e.district.zoneNumber as zoneNumber)from Estate e  " +
        //                        "where e.city" +
        //                        ".zoneNumber ='${city}' group by e.district.name,e.district.zoneNumber"
        //                def results1 = Estate.executeQuery(sql1).asList()
        //                mapData=results1
        //            }
        //            if(type=='2'){
        //                //每个行政区的贷款余额
        //                def sql2 = "select new map(sum(e.loansBalance) as loansBalance,e.district.name as cityName,e.district.zoneNumber as zoneNumber )from " +
        //                        "Estate e where e.city.zoneNumber ='${city}' group by e.district.name,e.district.zoneNumber"
        //                def results2 = Estate.executeQuery(sql2).asList()
        //                mapData=results2
        //            }
        //            if(type=='3'){
        //                //每个行政区的 动态抵押率=贷款余额/押品价值
        //                def sql3 = "select new map(sum(e.loansBalance) as loansBalance, sum(e.loansEvaluatePrice)  as " +
        //                        "loansEvaluatePrice ,e.district.name as  cityName ,e.district.zoneNumber as zoneNumber)from Estate e  where e.city.zoneNumber =" +
        //                        "'${city}' group by e.district.name,e.district.zoneNumber"
        //                def results3 = Estate.executeQuery(sql3).asList()
        //                results3.each {
        //                    def dynamicMortgageRate = it.loansBalance / it.loansEvaluatePrice
        //                    it.dynamicMortgageRate = dynamicMortgageRate.setScale(2, BigDecimal.ROUND_HALF_UP)
        //                }
        //                mapData=results3
        //            }
        //        }
        render([mapData: mapData] as JSON)

    }

    //地图数据
    def mapData1()
    {
        def mapData = new HashMap()
        def city = params["city"]
        def type = params["type"]
        def noBeijingSql = " and e.city.id <> 2 "
        //        if(city == '-1'){
        //获取用户开通的城市
        //            def cityList = this.getCityListByUser()
        if (type == '1')
        {
            //每个城市的押品数量
            def sql1 = "select new map(count(e.id) as value,e.city.name as cityName)" + "from Estate e where 1=1 " + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " group by e.city.name"
            def cityResults = Estate.executeQuery(sql1).asList()
            //每个省的押品
            def sql2 = "select new map(count(e.id) as value,e.province.name as cityName " + ")  from Estate e  where e.province.name not in (:provinceList) " + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " group by e.province.name"
            def provinceResults = Estate.executeQuery(sql2, ["provinceList": ['北京市', '重庆市', '上海市', '天津市']]).asList()
            //每个行政区的
            def sql3 = "select new map(count(e.id) as value,e.district.name as cityName)from Estate e where 1=1 " + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  group by e" + ".district.name "
            def districtResults = Estate.executeQuery(sql3).asList()
            provinceResults.addAll(cityResults)
            provinceResults.addAll(districtResults)
            mapData = provinceResults
        }
        if (type == '2')
        {
            //每个城市的贷款余额
            def sql1 = "select new map(sum(e.loansBalance) as value,e.city.name as cityName )from Estate e where 1=1  " + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  group by e.city.name"
            def cityResults = Estate.executeQuery(sql1).asList()
            //省
            def sql2 = "select new map(sum(e.loansBalance) as value,e.province.name as cityName )from Estate e " + " where e.province.name not in (:provinceList) " + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " group by e.province.name"
            def provinceResults = Estate.executeQuery(sql2, ["provinceList": ['北京市', '重庆市', '上海市', '天津市']]).asList()
            //行政区
            def sql3 = "select new map(sum(e.loansBalance) as value,e.district.name as cityName)from " + "Estate e where 1=1  " + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  group by e.district.name"
            def districtResults = Estate.executeQuery(sql3).asList()
            provinceResults.addAll(cityResults)
            provinceResults.addAll(districtResults)
            mapData = provinceResults
        }
        if (type == '3')
        {
            //省
            def sql1 = "select new map(sum(e.loansBalance) as loansBalance, sum(e.totalPrice)  as " + "loansEvaluatePrice ,e.province.name as  cityName )from Estate e " + " where e.province.name not in (:provinceList) " + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " group by e.province.name"
            def provinceResults = Estate.executeQuery(sql1, ["provinceList": ['北京市', '重庆市', '上海市', '天津市']]).asList()

            //每个城市的 动态抵押率=贷款余额/押品价值
            def sql2 = "select new map(sum(e.loansBalance) as loansBalance, sum(e.totalPrice)  as " + "loansEvaluatePrice ,e.city.name as  cityName )from Estate e where 1=1  " + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " group by e.city.name "
            def cityResults = Estate.executeQuery(sql2).asList()
            //区
            //每个城市的 动态抵押率=贷款余额/押品价值
            def sql3 = "select new map(sum(e.loansBalance) as loansBalance, sum(e.totalPrice)  as " + "loansEvaluatePrice ,e.district.name as  cityName )from Estate e where 1=1  " + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  group by e.district.name"
            def districtResults = Estate.executeQuery(sql3).asList()
            provinceResults.addAll(cityResults)
            provinceResults.addAll(districtResults)
            provinceResults.each {
                if (it.loansBalance && it.loansEvaluatePrice)
                {
                    def dynamicMortgageRate = it.loansBalance / it.loansEvaluatePrice
                    it.value = dynamicMortgageRate.setScale(4, BigDecimal.ROUND_HALF_UP) * 100
                }
            }

            mapData = provinceResults
        }
        if (type == '4')
        {
            //每个城市的命中反欺诈数
            def sql1 = "select new map(count(e1.id) as estateCount,count(case when e1.isAntiFraud = true then 1 else null end ) as value,e.city.name as cityName )from Mortgage e1 left join e1.estate e on 1=1 where 1=1  " + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  group by e.city.name"
            def cityResults = Estate.executeQuery(sql1).asList()
            //省
            def sql2 = "select new map(count(e1.id) as estateCount,count(case when e1.isAntiFraud = true then 1 else null end ) as value,e.province.name as cityName )from Mortgage e1 left join e1.estate e on 1=1 where e.province.name not in (:provinceList) " + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " group by e.province.name"
            def provinceResults = Estate.executeQuery(sql2, ["provinceList": ['北京市', '重庆市', '上海市', '天津市']]).asList()
            //行政区
            def sql3 = "select new map(count(e1.id) as estateCount,count(case when e1.isAntiFraud = true then 1 else null end ) as value,e.district.name as cityName)from " + "Mortgage e1 left join e1.estate e on 1=1 where 1=1  " + noBeijingSql + " and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  group by e.district.name"
            def districtResults = Estate.executeQuery(sql3).asList()
            provinceResults.addAll(cityResults)
            provinceResults.addAll(districtResults)
            mapData = provinceResults
        }
        render([mapData: mapData] as JSON)

    }

    def mapDataProvince()
    {
        def type = params["type"]
        def mapdata
        if (type == '2')
        {
            //省
            def sql2 = "select new map(sum(e.loansBalance) as value,e.province.name as cityName )from Estate e " + "  group by e.province.name"
            mapdata = Estate.executeQuery(sql2).asList()
        }
        else if (type == '3')
        {
            //省
            def sql1 = "select new map(sum(e.loansBalance) as loansBalance, sum(e.loansEvaluateTotalPrice)  as " + "loansEvaluatePrice ,e.province.name as  cityName )from Estate e " + "  group by e.province.name"
            mapdata = Estate.executeQuery(sql1).asList()
            mapdata.each {
                if (it.loansBalance && it.loansEvaluatePrice)
                {
                    def dynamicMortgageRate = it.loansBalance / it.loansEvaluatePrice
                    it.value = dynamicMortgageRate.setScale(2, BigDecimal.ROUND_HALF_UP)
                }
            }
        }
        render([mapData: mapdata] as JSON)
    }

    def mapDataCity()
    {
        def province = params["province"]
        def type = params["type"]
        def mapdata
        if (type == '2')
        {
            //每个城市的贷款余额
            def sql1 = "select new map(sum(e.loansBalance) as value,e.city.name as cityName )from Estate e " + " where e.province.name ='${province}' group by e.city.name"
            mapdata = Estate.executeQuery(sql1).asList()

        }
        else if (type == '3')
        {
            //每个城市的 动态抵押率=贷款余额/押品价值
            def sql2 = "select new map(sum(e.loansBalance) as loansBalance, sum(e.loansEvaluateTotalPrice)  as " + "loansEvaluatePrice ,e.city.name as  cityName )from Estate e " + " where e.province.name ='${province}' group by e.city.name "
            mapdata = Estate.executeQuery(sql2).asList()
            mapdata.each {
                if (it.loansBalance && it.loansEvaluatePrice)
                {
                    def dynamicMortgageRate = it.loansBalance / it.loansEvaluatePrice
                    it.value = dynamicMortgageRate.setScale(2, BigDecimal.ROUND_HALF_UP)
                }
            }
        }
        render([mapData: mapdata] as JSON)
    }

    def mapDataDistrict()
    {
        def city = params["city"]
        def type = params["type"]
        def mapdata
        if (type == '2')
        {
            //行政区
            def sql3 = "select new map(sum(e.loansBalance) as value,e.district.name as cityName)from " + "Estate e where e.city.zoneNumber='${city}' group by e.district.name"
            mapdata = Estate.executeQuery(sql3).asList()
        }
        else if (type == '3')
        {
            def sql3 = "select new map(sum(e.loansBalance) as loansBalance, sum(e.loansEvaluateTotalPrice)  as " + "loansEvaluatePrice ,e.district.name as  cityName )from Estate e " + " where e.city.zoneNumber='${city}' group by e.district.name"
            mapdata = Estate.executeQuery(sql3).asList()
            mapdata.each {
                if (it.loansBalance && it.loansEvaluatePrice)
                {
                    def dynamicMortgageRate = it.loansBalance / it.loansEvaluatePrice
                    it.value = dynamicMortgageRate.setScale(2, BigDecimal.ROUND_HALF_UP)
                }
            }
        }
        render([mapData: mapdata] as JSON)
    }

    //柱状图数据
    def columnarData()
    {
        def cityList = this.getCityListByUser()
        def city = params["city"]
        def level = params["level"]
        def columnarData = new HashMap()
        def zoneNumber = params["zoneNumber"]
        def max = params.max ? params.max.toInteger() : 5
        def noBeijingSql = " and a.id <> 2 "
        if ((city == '-1' || zoneNumber == '-1') && level == '1')
        {
            //全国下的市
            def allCountList = Area.executeQuery("select count(a.id) from Area a where a.areaLevel = 2 " + noBeijingSql)
            def allCount = allCountList[0]
            int offset = allCount - max
            //TODO 调接口获取城市的住宅总套数 押品总数和住宅总数
            //def countSql = "select new map(count(e.id) as estateCount,e.city.zoneNumber as zoneNumber,e.city.name as " + "cityName) from  Estate e  group by e.city.name,e.city.zoneNumber order by estateCount "
            def countSql = " select new map ( count(e.id) as estateCount,a.name as cityName ,a.zoneNumber as zoneNumber,a.id as id) from Estate e  right join e.city a on 1=1 and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " where a.areaLevel = 2 " + noBeijingSql + "  group by a.name,a.zoneNumber,a.id order by count(e.id) ,a.id desc"
            def countResult = Estate.executeQuery(countSql, [max: max, offset: offset]).asList()
            countResult.each {
                it.allCount = 1000
            }
            columnarData.estateCount = countResult
            //贷款余额和贷款总额
            //def loansSql = "select new map(sum(e.loansAmount) as loansAmountTotal,sum(e.loansBalance) as " + "loansBalanceTotal,e.city.name as cityName,e.city.zoneNumber as zoneNumber )from Estate e " + "group by e.city.name,e.city.zoneNumber  order by loansAmountTotal"
            def loansSql = " select new map ( CASE WHEN sum(e.loansAmount) IS NULL THEN 0.0 ELSE sum(e.loansAmount) END  as loansAmountTotal,CASE WHEN sum(e.loansBalance) IS NULL THEN 0.0 ELSE sum(e.loansBalance) END as loansBalanceTotal,a.name as cityName ,a.zoneNumber as zoneNumber,a.id as id) from Estate e right join e.city a on 1=1 and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " where a.areaLevel = 2 " + noBeijingSql + " group by a.name,a.zoneNumber,a.id order by loansAmountTotal,a.id desc "
            def loansResult = Estate.executeQuery(loansSql, [max: max, offset: offset]).asList()
            columnarData.loansBalance = loansResult

            //每个城市的 动态抵押率=贷款余额/押品价值
            //def rateSql = "select new map(sum(e.loansBalance) as loansBalance, sum(e.totalPrice)  as  " + "loansEvaluatePrice ,e.city.name as  cityName,e.city.zoneNumber as zoneNumber )from Estate e " + "group by e.city.name,e.city.zoneNumber order by loansEvaluatePrice"
            def rateSql = " select new map ( CASE WHEN sum(e.loansBalance) IS NULL THEN 0.0 ELSE sum(e.loansBalance) END  as loansBalance,CASE WHEN sum(e.totalPrice) IS NULL THEN 0.0 ELSE sum(e.totalPrice) END as loansEvaluatePrice,a.name as cityName ,a.zoneNumber as zoneNumber,a.id as id) from Estate e right join e.city a on 1=1 and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  where a.areaLevel = 2 " + noBeijingSql + " group by a.name,a.zoneNumber,a.id order by loansEvaluatePrice ,loansBalance ,a.id desc "
            def rateResult = Estate.executeQuery(rateSql, [max: max, offset: offset]).asList()
            rateResult.each {
                if (it.loansBalance == 0 || it.loansEvaluatePrice == 0)
                {
                    it.dynamicMortgageRate = 0.00
                }
                else
                {
                    def dynamicMortgageRate = it.loansBalance / it.loansEvaluatePrice
                    //it.dynamicMortgageRate = dynamicMortgageRate.setScale(2, BigDecimal.ROUND_HALF_UP)
                    it.dynamicMortgageRate = dynamicMortgageRate
                }
            }
            columnarData.dynamicMortgageRate = rateResult
        }
        else if (city != '-1' && city && level == '2')
        {
            //省下的市
            //def countSql = "select new map(count(e.id) as estateCount,e.city.zoneNumber as  zoneNumber,e" + ".city.name as cityName) from Estate e where e.province.name ='${city}' group by e" + ".city.name,e.city.zoneNumber order by estateCount"
            def countSql = " select new map ( count(e.id) as estateCount,a.name as cityName ,a.zoneNumber as zoneNumber,a.id as id) from Estate e right join e.city a on 1=1 and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  where a.areaLevel = 2 and a.parentArea.name ='${city}' " + noBeijingSql + " group by a.name,a.zoneNumber,a.id order by count(e.id) ,a.id desc "
            def countResult = Estate.executeQuery(countSql).asList()
            countResult.each {
                it.allCount = 1000
            }
            columnarData.estateCount = countResult
            //贷款余额和贷款总额
            //def sql1 = "select new map(sum(e.loansAmount) as loansAmountTotal,sum(e.loansBalance) as " + "loansBalanceTotal,e.city.name as cityName,e.city.zoneNumber as  zoneNumber) from " + "Estate" + " e where e.province.name ='${city}' group by e.city.name,e.city.zoneNumber order by loansAmountTotal"
            def sql1 = " select new map ( CASE WHEN sum(e.loansAmount) IS NULL THEN 0.0 ELSE sum(e.loansAmount) END  as loansAmountTotal,CASE WHEN sum(e.loansBalance) IS NULL THEN 0.0 ELSE sum(e.loansBalance) END as loansBalanceTotal,a.name as cityName ,a.zoneNumber as zoneNumber,a.id as id) from Estate e right join e.city a on 1=1 and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  where a.areaLevel = 2 and a.parentArea.name ='${city}' " + noBeijingSql + " group by a.name,a.zoneNumber,a.id order by loansAmountTotal ,a.id desc "
            def query = Estate.executeQuery(sql1).asList()
            columnarData.loansBalance = query

            //每个城市的 动态抵押率=贷款余额/押品价值
            //def sql2 = "select new map(sum(e.loansBalance) as loansBalance, sum(e.totalPrice)  as  " + "loansEvaluatePrice ,e.city.name as  cityName,e.city.zoneNumber as  zoneNumber )from " + "Estate e where e.province.name='${city}'  group by e.city.name, e.city.zoneNumber order " + "by loansEvaluatePrice"
            def sql2 = " select new map ( CASE WHEN sum(e.loansBalance) IS NULL THEN 0.0 ELSE sum(e.loansBalance) END  as loansBalance,CASE WHEN sum(e.totalPrice) IS NULL THEN 0.0 ELSE sum(e.totalPrice) END as loansEvaluatePrice,a.name as cityName ,a.zoneNumber as zoneNumber,a.id as id) from Estate e right join e.city a on 1=1 and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  where a.areaLevel = 2 and a.parentArea.name ='${city}' " + noBeijingSql + " group by a.name,a.zoneNumber,a.id order by loansEvaluatePrice ,loansBalance ,a.id desc "
            def result = Estate.executeQuery(sql2).asList()
            result.each {
                if (it.loansBalance == 0 || it.loansEvaluatePrice == 0)
                {
                    it.dynamicMortgageRate = 0.00
                }
                else
                {
                    def dynamicMortgageRate = it.loansBalance / it.loansEvaluatePrice
                    //it.dynamicMortgageRate = dynamicMortgageRate.setScale(2, BigDecimal.ROUND_HALF_UP)
                    it.dynamicMortgageRate = dynamicMortgageRate
                }
            }
            columnarData.dynamicMortgageRate = result
        }
        else if (zoneNumber != '-1' && zoneNumber && level == '2')
        {
            def countSql = "select new map(count(e.id) as estateCount,e.district" + ".name as cityName,e.district.zoneNumber as zoneNumber) from Estate " + "e" + " where e" + ".city.zoneNumber =''${zoneNumber}'' group by e.district.name,e.district.zoneNumber order by estateCount"
            def countResult = Estate.executeQuery(countSql)
            countResult.each {
                it.allCount = 1000
            }
            columnarData.estateCount = countResult
            //贷款余额和贷款总额
            def sql1 = "select new map(sum(e.loansAmount) as loansAmountTotal,sum(e.loansBalance) as " + "loansBalanceTotal,e.district.name as cityName,e.district.zoneNumber as zoneNumber) from " + "Estate e where e.city.zoneNumber ='${zoneNumber}' group by e.district.name,e.district.zoneNumber order by loansAmountTotal"
            def query = Estate.executeQuery(sql1)
            columnarData.loansBalance = query

            //每个城市的 动态抵押率=贷款余额/押品价值
            def sql2 = "select new map(sum(e.loansBalance) as loansBalance, sum(e.totalPrice)  as  " + "loansEvaluatePrice ,e.district.name as  cityName,e.district.zoneNumber as  zoneNumber )from " + "Estate e where e.city.zoneNumber='${zoneNumber}'" + " group by e.district.name, e.district.zoneNumber order by loansEvaluatePrice"
            def result = Estate.executeQuery(sql2).asList()
            result.each {
                if (it.loansBalance && it.loansEvaluatePrice)
                {
                    //todo 取小数点
                    def dynamicMortgageRate = it.loansBalance / it.loansEvaluatePrice
                    it.dynamicMortgageRate = dynamicMortgageRate.setScale(2, BigDecimal.ROUND_HALF_UP)
                }
            }
            columnarData.dynamicMortgageRate = result
        }
        else if (city != '-1' && city && level == '3')
        {
            def countSql = "select new map(count(e.id) as estateCount,e.district.zoneNumber as  zoneNumber,e" + ".district.name as cityName) from Estate e where e.city.name ='${city}' group by e" + ".district.name,e.district.zoneNumber order by estateCount"
            def countResult = Estate.executeQuery(countSql)
            countResult.each {
                it.allCount = 1000
            }
            columnarData.estateCount = countResult
            //贷款余额和贷款总额
            def sql1 = "select new map(sum(e.loansAmount) as loansAmountTotal,sum(e.loansBalance) as " + "loansBalanceTotal,e.district.name as cityName,e.district.zoneNumber as  zoneNumber) from " + "Estate e where e.city.name ='${city}' group by e.district.name,e.district.zoneNumber order by loansAmountTotal"
            def query = Estate.executeQuery(sql1)
            columnarData.loansBalance = query

            //每个城市的 动态抵押率=贷款余额/押品价值
            def sql2 = "select new map(sum(e.loansBalance) as loansBalance, sum(e.totalPrice)  as  " + "loansEvaluatePrice ,e.district.name as  cityName,e.district.zoneNumber as  zoneNumber )from " + "Estate e where e.city.name='${city}'" + " group by e.district.name, e.district.zoneNumber order by loansEvaluatePrice"
            def result = Estate.executeQuery(sql2).asList()
            result.each {
                if (it.loansBalance && it.loansEvaluatePrice)
                {
                    // 取小数点
                    def dynamicMortgageRate = it.loansBalance / it.loansEvaluatePrice
                    it.dynamicMortgageRate = dynamicMortgageRate.setScale(2, BigDecimal.ROUND_HALF_UP)
                }

            }
            columnarData.dynamicMortgageRate = result
        }
        else if (zoneNumber != '-1' && zoneNumber && level == '3')
        {
            //市下的行政区
            //def countSql = "select new map(count(e.id) as estateCount,e.district.name as cityName,e.district" + ".zoneNumber as zoneNumber " + ") from Estate " + "e where e" + ".city.zoneNumber ='${zoneNumber}' group by e.district.name,e.district.zoneNumber order by estateCount"
            def countSql = " select new map ( count(e.id) as estateCount,a.name as cityName ,a.zoneNumber as zoneNumber,a.id as id) from Estate e right join e.district a on 1=1 and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  where a.areaLevel = 3 and a.parentArea.zoneNumber ='${zoneNumber}' " + noBeijingSql + " group by a.name,a.zoneNumber,a.id order by count(e.id) ,a.id desc "
            def countResult = Estate.executeQuery(countSql).asList()
            countResult.each {
                it.allCount = 1000
            }
            columnarData.estateCount = countResult
            //贷款余额和贷款总额
            //def sql1 = "select new map(sum(e.loansAmount) as loansAmountTotal,sum(e.loansBalance) as " + "loansBalanceTotal,e.district.name as cityName,e.district.zoneNumber as zoneNumber) from " + "Estate e where e.city.zoneNumber ='${zoneNumber}' group by e.district.name,e.district.zoneNumber order by loansAmountTotal"
            def sql1 = " select new map ( CASE WHEN sum(e.loansAmount) IS NULL THEN 0.0 ELSE sum(e.loansAmount) END  as loansAmountTotal,CASE WHEN sum(e.loansBalance) IS NULL THEN 0.0 ELSE sum(e.loansBalance) END as loansBalanceTotal,a.name as cityName ,a.zoneNumber as zoneNumber,a.id as id) from Estate e right join e.district a on 1=1 and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  where a.areaLevel = 3 and a.parentArea.zoneNumber ='${zoneNumber}' " + noBeijingSql + " group by a.name,a.zoneNumber,a.id order by loansAmountTotal,a.id desc "
            def query = Estate.executeQuery(sql1).asList()
            columnarData.loansBalance = query

            //每个城市的 动态抵押率=贷款余额/押品价值
            //def sql2 = "select new map(sum(e.loansBalance) as loansBalance, sum(e.totalPrice)  as  " + "loansEvaluatePrice ,e.district.name as  cityName,e.district.zoneNumber as zoneNumber )from " + "Estate e where e.city" + ".zoneNumber='${zoneNumber}'" + " group by e.district.name,e.district.zoneNumber order by loansEvaluatePrice"
            def sql2 = " select new map ( CASE WHEN sum(e.loansBalance) IS NULL THEN 0.0 ELSE sum(e.loansBalance) END  as loansBalance,CASE WHEN sum(e.totalPrice) IS NULL THEN 0.0 ELSE sum(e.totalPrice) END as loansEvaluatePrice,a.name as cityName ,a.zoneNumber as zoneNumber,a.id as id) from Estate e right join e.district a on 1=1 and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  where a.areaLevel = 3 and a.parentArea.zoneNumber ='${zoneNumber}' " + noBeijingSql + " group by a.name,a.zoneNumber,a.id order by loansEvaluatePrice ,loansBalance ,a.id desc "
            def result = Estate.executeQuery(sql2).asList()
            result.each {
                if (it.loansBalance == 0 || it.loansEvaluatePrice == 0)
                {
                    it.dynamicMortgageRate = 0.00
                }
                else
                {
                    def dynamicMortgageRate = it.loansBalance / it.loansEvaluatePrice
                    //it.dynamicMortgageRate = dynamicMortgageRate.setScale(2, BigDecimal.ROUND_HALF_UP)
                    it.dynamicMortgageRate = dynamicMortgageRate
                }
            }
            columnarData.dynamicMortgageRate = result
        }
        render([dataResult: columnarData] as JSON)
    }

    //柱状图数据
    def columnarData1()
    {
        def cityList = this.getCityListByUser()
        def city = params["city"]
        def level = params["level"]
        def columnarData = new HashMap()
        def zoneNumber = params["zoneNumber"]
        def max = params.max ? params.max.toInteger() : 5
        def noBeijingSql = " and a.id <> 2 "
        if ((city == '-1' || zoneNumber == '-1') && level == '1')
        {
            //全国下的市
            def allCountList = Area.executeQuery("select count(a.id) from Area a where a.areaLevel = 2 " + noBeijingSql)
            def allCount = allCountList[0]
            int offset = allCount - max
            //TODO 调接口获取城市的住宅总套数 押品总数和住宅总数
            //def countSql = "select new map(count(e.id) as estateCount,e.city.zoneNumber as zoneNumber,e.city.name as " + "cityName) from  Estate e  group by e.city.name,e.city.zoneNumber order by estateCount "
            def countSql = " select new map ( count(e.id) as estateCount,a.name as cityName ,a.zoneNumber as zoneNumber,a.id as id) from Estate e  right join e.city a on 1=1 and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " where a.areaLevel = 2 " + noBeijingSql + "  group by a.name,a.zoneNumber,a.id order by count(e.id) ,a.id desc"
            def countResult = Estate.executeQuery(countSql, [max: max, offset: offset]).asList()
            countResult.each {
                it.allCount = 1000
            }
            columnarData.estateCount = countResult
            //贷款余额和贷款总额
            //def loansSql = "select new map(sum(e.loansAmount) as loansAmountTotal,sum(e.loansBalance) as " + "loansBalanceTotal,e.city.name as cityName,e.city.zoneNumber as zoneNumber )from Estate e " + "group by e.city.name,e.city.zoneNumber  order by loansAmountTotal"
            def loansSql = " select new map ( CASE WHEN sum(e.loansAmount) IS NULL THEN 0.0 ELSE sum(e.loansAmount) END  as loansAmountTotal,CASE WHEN sum(e.loansBalance) IS NULL THEN 0.0 ELSE sum(e.loansBalance) END as loansBalanceTotal,a.name as cityName ,a.zoneNumber as zoneNumber,a.id as id) from Estate e right join e.city a on 1=1 and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + " where a.areaLevel = 2 " + noBeijingSql + " group by a.name,a.zoneNumber,a.id order by loansAmountTotal,a.id desc "
            def loansResult = Estate.executeQuery(loansSql, [max: max, offset: offset]).asList()
            columnarData.loansBalance = loansResult

            //每个城市的 动态抵押率=贷款余额/押品价值
            //def rateSql = "select new map(sum(e.loansBalance) as loansBalance, sum(e.totalPrice)  as  " + "loansEvaluatePrice ,e.city.name as  cityName,e.city.zoneNumber as zoneNumber )from Estate e " + "group by e.city.name,e.city.zoneNumber order by loansEvaluatePrice"
            def rateSql = " select new map ( CASE WHEN sum(e.loansBalance) IS NULL THEN 0.0 ELSE sum(e.loansBalance) END  as loansBalance,CASE WHEN sum(e.totalPrice) IS NULL THEN 0.0 ELSE sum(e.totalPrice) END as loansEvaluatePrice,a.name as cityName ,a.zoneNumber as zoneNumber,a.id as id) from Estate e right join e.city a on 1=1 and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  where a.areaLevel = 2 " + noBeijingSql + " group by a.name,a.zoneNumber,a.id order by loansEvaluatePrice ,loansBalance ,a.id desc "
            def rateResult = Estate.executeQuery(rateSql, [max: max, offset: offset]).asList()
            rateResult.each {
                if (it.loansBalance == 0 || it.loansEvaluatePrice == 0)
                {
                    it.dynamicMortgageRate = 0.00
                }
                else
                {
                    def dynamicMortgageRate = it.loansBalance / it.loansEvaluatePrice
                    //it.dynamicMortgageRate = dynamicMortgageRate.setScale(2, BigDecimal.ROUND_HALF_UP)
                    it.dynamicMortgageRate = dynamicMortgageRate
                }
            }
            columnarData.dynamicMortgageRate = rateResult
            //每个城市的 总数，命中反欺诈数
            def antiFraudSql = " select new map ( count(e1.id) as estateCount,count(case when e1.isAntiFraud = true then 1 else null end ) as antiFraudCount,a.name as cityName ,a.zoneNumber as zoneNumber,a.id as id) from Mortgage e1 left join e1.estate e right join e.city a on 1=1 and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  where a.areaLevel = 2 " + noBeijingSql + " group by a.name,a.zoneNumber,a.id order by count(e1.id) ,a.id desc "
            def antiFraudResult = Estate.executeQuery(antiFraudSql, [max: max, offset: offset]).asList()
            columnarData.antiFraud = antiFraudResult
        }
        else if (city != '-1' && city && level == '2')
        {
            //省下的市
            //def countSql = "select new map(count(e.id) as estateCount,e.city.zoneNumber as  zoneNumber,e" + ".city.name as cityName) from Estate e where e.province.name ='${city}' group by e" + ".city.name,e.city.zoneNumber order by estateCount"
            def countSql = " select new map ( count(e.id) as estateCount,a.name as cityName ,a.zoneNumber as zoneNumber,a.id as id) from Estate e right join e.city a on 1=1 and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  where a.areaLevel = 2 and a.parentArea.name ='${city}' " + noBeijingSql + " group by a.name,a.zoneNumber,a.id order by count(e.id) ,a.id desc "
            def countResult = Estate.executeQuery(countSql).asList()
            countResult.each {
                it.allCount = 1000
            }
            columnarData.estateCount = countResult
            //贷款余额和贷款总额
            //def sql1 = "select new map(sum(e.loansAmount) as loansAmountTotal,sum(e.loansBalance) as " + "loansBalanceTotal,e.city.name as cityName,e.city.zoneNumber as  zoneNumber) from " + "Estate" + " e where e.province.name ='${city}' group by e.city.name,e.city.zoneNumber order by loansAmountTotal"
            def sql1 = " select new map ( CASE WHEN sum(e.loansAmount) IS NULL THEN 0.0 ELSE sum(e.loansAmount) END  as loansAmountTotal,CASE WHEN sum(e.loansBalance) IS NULL THEN 0.0 ELSE sum(e.loansBalance) END as loansBalanceTotal,a.name as cityName ,a.zoneNumber as zoneNumber,a.id as id) from Estate e right join e.city a on 1=1 and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  where a.areaLevel = 2 and a.parentArea.name ='${city}' " + noBeijingSql + " group by a.name,a.zoneNumber,a.id order by loansAmountTotal ,a.id desc "
            def query = Estate.executeQuery(sql1).asList()
            columnarData.loansBalance = query

            //每个城市的 动态抵押率=贷款余额/押品价值
            //def sql2 = "select new map(sum(e.loansBalance) as loansBalance, sum(e.totalPrice)  as  " + "loansEvaluatePrice ,e.city.name as  cityName,e.city.zoneNumber as  zoneNumber )from " + "Estate e where e.province.name='${city}'  group by e.city.name, e.city.zoneNumber order " + "by loansEvaluatePrice"
            def sql2 = " select new map ( CASE WHEN sum(e.loansBalance) IS NULL THEN 0.0 ELSE sum(e.loansBalance) END  as loansBalance,CASE WHEN sum(e.totalPrice) IS NULL THEN 0.0 ELSE sum(e.totalPrice) END as loansEvaluatePrice,a.name as cityName ,a.zoneNumber as zoneNumber,a.id as id) from Estate e right join e.city a on 1=1 and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  where a.areaLevel = 2 and a.parentArea.name ='${city}' " + noBeijingSql + " group by a.name,a.zoneNumber,a.id order by loansEvaluatePrice ,loansBalance ,a.id desc "
            def result = Estate.executeQuery(sql2).asList()
            result.each {
                if (it.loansBalance == 0 || it.loansEvaluatePrice == 0)
                {
                    it.dynamicMortgageRate = 0.00
                }
                else
                {
                    def dynamicMortgageRate = it.loansBalance / it.loansEvaluatePrice
                    //it.dynamicMortgageRate = dynamicMortgageRate.setScale(2, BigDecimal.ROUND_HALF_UP)
                    it.dynamicMortgageRate = dynamicMortgageRate
                }
            }
            columnarData.dynamicMortgageRate = result
            //总数，命中反欺诈数
            def antiFraudSql = " select new map (  count(e1.id) as estateCount,count(case when e1.isAntiFraud = true then 1 else null end ) as antiFraudCount,a.name as cityName ,a.zoneNumber as zoneNumber,a.id as id) from Mortgage e1 left join e1.estate e on 1=1 right join e.city a on 1=1 and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  where a.areaLevel = 2 and a.parentArea.name ='${city}' " + noBeijingSql + " group by a.name,a.zoneNumber,a.id order by count(e1.id) ,a.id desc "
            def antiFraudResult = Estate.executeQuery(antiFraudSql).asList()
            columnarData.antiFraud = antiFraudResult
        }
        else if (zoneNumber != '-1' && zoneNumber && level == '3')
        {
            //市下的行政区
            //def countSql = "select new map(count(e.id) as estateCount,e.district.name as cityName,e.district" + ".zoneNumber as zoneNumber " + ") from Estate " + "e where e" + ".city.zoneNumber ='${zoneNumber}' group by e.district.name,e.district.zoneNumber order by estateCount"
            def countSql = " select new map ( count(e.id) as estateCount,a.name as cityName ,a.zoneNumber as zoneNumber,a.id as id) from Estate e right join e.district a on 1=1 and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  where a.areaLevel = 3 and a.parentArea.zoneNumber ='${zoneNumber}' " + noBeijingSql + " group by a.name,a.zoneNumber,a.id order by count(e.id) ,a.id desc "
            def countResult = Estate.executeQuery(countSql).asList()
            countResult.each {
                it.allCount = 1000
            }
            columnarData.estateCount = countResult
            //贷款余额和贷款总额
            //def sql1 = "select new map(sum(e.loansAmount) as loansAmountTotal,sum(e.loansBalance) as " + "loansBalanceTotal,e.district.name as cityName,e.district.zoneNumber as zoneNumber) from " + "Estate e where e.city.zoneNumber ='${zoneNumber}' group by e.district.name,e.district.zoneNumber order by loansAmountTotal"
            def sql1 = " select new map ( CASE WHEN sum(e.loansAmount) IS NULL THEN 0.0 ELSE sum(e.loansAmount) END  as loansAmountTotal,CASE WHEN sum(e.loansBalance) IS NULL THEN 0.0 ELSE sum(e.loansBalance) END as loansBalanceTotal,a.name as cityName ,a.zoneNumber as zoneNumber,a.id as id) from Estate e right join e.district a on 1=1 and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  where a.areaLevel = 3 and a.parentArea.zoneNumber ='${zoneNumber}' " + noBeijingSql + " group by a.name,a.zoneNumber,a.id order by loansAmountTotal,a.id desc "
            def query = Estate.executeQuery(sql1).asList()
            columnarData.loansBalance = query

            //每个城市的 动态抵押率=贷款余额/押品价值
            //def sql2 = "select new map(sum(e.loansBalance) as loansBalance, sum(e.totalPrice)  as  " + "loansEvaluatePrice ,e.district.name as  cityName,e.district.zoneNumber as zoneNumber )from " + "Estate e where e.city" + ".zoneNumber='${zoneNumber}'" + " group by e.district.name,e.district.zoneNumber order by loansEvaluatePrice"
            def sql2 = " select new map ( CASE WHEN sum(e.loansBalance) IS NULL THEN 0.0 ELSE sum(e.loansBalance) END  as loansBalance,CASE WHEN sum(e.totalPrice) IS NULL THEN 0.0 ELSE sum(e.totalPrice) END as loansEvaluatePrice,a.name as cityName ,a.zoneNumber as zoneNumber,a.id as id) from Estate e right join e.district a on 1=1 and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  where a.areaLevel = 3 and a.parentArea.zoneNumber ='${zoneNumber}' " + noBeijingSql + " group by a.name,a.zoneNumber,a.id order by loansEvaluatePrice ,loansBalance ,a.id desc "
            def result = Estate.executeQuery(sql2).asList()
            result.each {
                if (it.loansBalance == 0 || it.loansEvaluatePrice == 0)
                {
                    it.dynamicMortgageRate = 0.00
                }
                else
                {
                    def dynamicMortgageRate = it.loansBalance / it.loansEvaluatePrice
                    //it.dynamicMortgageRate = dynamicMortgageRate.setScale(2, BigDecimal.ROUND_HALF_UP)
                    it.dynamicMortgageRate = dynamicMortgageRate
                }
            }
            columnarData.dynamicMortgageRate = result

            //总数,命中反欺诈数
            def antiFraudSql = " select new map (  count(e1.id) as estateCount,count(case when e1.isAntiFraud = true then 1 else null end ) as antiFraudCount,a.name as cityName ,a.zoneNumber as zoneNumber,a.id as id) from Mortgage e1 left join e1.estate e right join e.district a on 1=1 and e.account.id in " + Account.FilterAccountSql(User.findById(springSecurityService.principal.id)) + "  where a.areaLevel = 3 and a.parentArea.zoneNumber ='${zoneNumber}' " + noBeijingSql + " group by a.name,a.zoneNumber,a.id order by count(e1.id),a.id desc "
            def antiFraudResult = Estate.executeQuery(antiFraudSql).asList()
            columnarData.antiFraud = antiFraudResult
        }
        render([dataResult: columnarData] as JSON)
    }

    def getCityListByUser()
    {
        //获取城市列表
        def sql = "select distinct e.city as city from  Estate e "
        def query = Estate.executeQuery(sql)
        return query
    }

    //统计数据 1
    //    def statisticsNew()
    //    {
    //        def province = params["province"]
    //        def city = params["city"]
    //        def zoneNumber = params["zoneNumber"]
    //        def level = params["level"]
    //        //当月第一天
    //        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
    //        Calendar c = Calendar.getInstance();
    //        c.add(Calendar.MONTH, 0);
    //        c.set(Calendar.DAY_OF_MONTH, 1); //设置为1号,当前日期既为本月第一天
    //        String first = format.format(c.getTime());
    //        //当月最后一天
    //        Calendar ca = Calendar.getInstance();
    //        ca.set(Calendar.DAY_OF_MONTH, ca.getActualMaximum(Calendar.DAY_OF_MONTH));
    //        String last = format.format(ca.getTime());
    //        def result
    //        def monthResult
    //        def sql = "select new map(sum(e.loansAmount) as loansAmount,sum(e.loansBalance) as loansBalance, sum(e" + ".buildArea) as buildArea,sum(e.count) as estateCount,sum(e.loansEvaluateTotalPrice) as " + "loansEvaluatePrice) from "
    //        def monthSql = "select new map(count(e.id) as  count)from Estate e "
    //        if (city == '-1') //全国
    //        {
    //            //            city = this.getCityListByUser()
    //            //            sql += " where  e.city.zoneNumber in (:cityList)"
    //            //            monthSql+="where e.city.zoneNumber in (:cityList) and e.startDate >'${first}' and e.startDate <='${last}'"
    //            sql += " ProvinceDataView  e "
    //            monthSql += "where  e.startDate >'${first}' and e.startDate <='${last}'"
    //            result = ProvinceDataView.executeQuery(sql)
    //            monthResult = Estate.executeQuery(monthSql).asList()
    //        }
    //        else if (city != '-1' && city)
    //        {
    //            sql += " CityDataView  e where  e.areaName = '${city}'"
    //            monthSql += "where e.city.zoneNumber = '${city}' and e.startDate >'${first}' and e.startDate <='${last}'"
    //            result = CityDataView.executeQuery(sql)
    //            monthResult = Estate.executeQuery(monthSql).asList()
    //        }
    //        else if (zoneNumber != '-1' && zoneNumber)
    //        {
    //            sql += " CityDataView  e where  e.zoneNumber = ''${zoneNumber}''"
    //            monthSql += "where e.city.zoneNumber = ''${zoneNumber}'' and e.startDate >'${first}' and e.startDate <='${last}'"
    //            result = CityDataView.executeQuery(sql)
    //            monthResult = Estate.executeQuery(monthSql).asList()
    //        }
    //        else
    //        {
    //            render([dataResult: null] as JSON)
    //        }
    //
    //        result[0].monthResult = monthResult[0].count
    //        render([dataResult: result] as JSON)
    //    }
    //
    //    //地图数据
    //    def mapDataNew()
    //    {
    //        def mapData = new HashMap()
    //        def city = params["city"]
    //        def type = params["type"]
    //        def level = params["level"]
    //        //        if(city == '-1'){
    //        //获取用户开通的城市
    //        //            def cityList = this.getCityListByUser()
    //        if (type == '1')
    //        {
    //            //每个城市的押品数量
    //            def sql1 = "select new map(e.count as value,e.areaName as cityName,e.zoneNumber as zoneNumber)" + "from CityDataView e  "
    //            def cityResults = CityDataView.executeQuery(sql1).asList()
    //            //每个省的押品
    //            def sql2 = "select new map(e.count as value,e.areaName as cityName,e.zoneNumber as " + "zoneNumber)  from" + " ProvinceDataView e  where e.areaName not in (:cityList) "
    //            def provinceResults = ProvinceDataView.executeQuery(sql2, [cityList: ['北京市', '重庆市', '上海市', '天津市']]).asList()
    //            //每个行政区的
    //            def sql3 = "select new map(e.count as value,e.areaName as cityName,e.zoneNumber as " + "zoneNumber)from DistrictDataView e "
    //            def districtResults = DistrictDataView.executeQuery(sql3).asList()
    //            provinceResults.addAll(cityResults)
    //            provinceResults.addAll(districtResults)
    //            mapData = provinceResults
    //        }
    //        if (type == '2')
    //        {
    //            //每个城市的贷款余额
    //            def sql1 = "select new map(e.loansBalance as value,e.areaName as cityName ,e.zoneNumber as zoneNumber)from " + "CityDataView e"
    //            def cityResults = CityDataView.executeQuery(sql1).asList()
    //            //省
    //            def sql2 = "select new map(e.loansBalance as value,e.areaName as cityName ,e.zoneNumber as zoneNumber)" + "from ProvinceDataView e where e.areaName not in (:cityList) "
    //            def provinceResults = ProvinceDataView.executeQuery(sql2, [cityList: ['北京市', '重庆市', '上海市', '天津市']]).asList()
    //            //行政区
    //            def sql3 = "select new map(e.loansBalance as value,e.areaName as cityName,e.zoneNumber as zoneNumber )from " + "DistrictDataView e "
    //            def districtResults = DistrictDataView.executeQuery(sql3).asList()
    //            provinceResults.addAll(cityResults)
    //            provinceResults.addAll(districtResults)
    //            mapData = provinceResults
    //        }
    //        if (type == '3')
    //        {
    //            //省
    //            def sql1 = "select new map(e.loansBalance as loansBalance, e.loansEvaluateTotalPrice as " + "loansEvaluatePrice ,e.areaName as  cityName,e.zoneNumber as zoneNumber )from ProvinceDataView e "
    //            def provinceResults = ProvinceDataView.executeQuery(sql1).asList()
    //
    //            //每个城市的 动态抵押率=贷款余额/押品价值
    //            def sql2 = "select new map(e.loansBalance as loansBalance, e.loansEvaluateTotalPrice  as " + "loansEvaluatePrice ,e.areaName as  cityName,e.zoneNumber as zoneNumber )from CityDataView e  where e.areaName not in (:cityList) "
    //            def cityResults = CityDataView.executeQuery(sql2, [cityList: ['北京市', '重庆市', '上海市', '天津市']]).asList()
    //            //区
    //            //每个城市的 动态抵押率=贷款余额/押品价值
    //            def sql3 = "select new map(e.loansBalance as loansBalance, e.loansEvaluateTotalPrice  as " + "loansEvaluatePrice ,e.areaName as  cityName,e.zoneNumber as zoneNumber )from DistrictDataView e "
    //            def districtResults = DistrictDataView.executeQuery(sql3).asList()
    //            provinceResults.addAll(cityResults)
    //            provinceResults.addAll(districtResults)
    //            provinceResults.each {
    //                def dynamicMortgageRate = it.loansBalance / it.loansEvaluatePrice
    //                it.value = dynamicMortgageRate.setScale(2, BigDecimal.ROUND_HALF_UP)
    //            }
    //
    //            mapData = provinceResults
    //        }
    //
    //        render([mapData: mapData] as JSON)
    //
    //    }
    //
    //    //柱状图数据
    //    def columnarDataNew()
    //    {
    //        def cityList = this.getCityListByUser()
    //        def city = params["city"]
    //        def zoneNumber = params["zoneNumber"]
    //        def columnarData = new HashMap()
    //
    //        if (city == '-1')
    //        {
    //
    //            //TODO 调接口获取城市的住宅总套数 押品总数和住宅总数
    //            def countSql = "select new map(e.count as estateCount,e.zoneNumber as zoneNumber,e.areaName" + " as cityName) from  ProvinceDataView e   order by estateCount "
    //            def countResult = ProvinceDataView.executeQuery(countSql)
    //            countResult.each {
    //                it.allCount = 1000
    //            }
    //            columnarData.estateCount = countResult
    //            //贷款余额和贷款总额
    //            def loansSql = "select new map(e.loansAmount as loansAmountTotal,e.loansBalance as " + "loansBalanceTotal,e.areaName as cityName,e.zoneNumber as zoneNumber )from ProvinceDataView e " + " order by loansAmountTotal"
    //            def loansResult = ProvinceDataView.executeQuery(loansSql)
    //            columnarData.loansBalance = loansResult
    //
    //            //每个城市的 动态抵押率=贷款余额/押品价值
    //            def rateSql = "select new map(e.loansBalance as loansBalance, e.loansEvaluateTotalPrice  as  " + "loansEvaluatePrice ,e.areaName as  cityName,e.zoneNumber as zoneNumber )from ProvinceDataView e " + " order by loansEvaluatePrice"
    //            def rateResult = ProvinceDataView.executeQuery(rateSql).asList()
    //            rateResult.each {
    //                def dynamicMortgageRate = it.loansBalance / it.loansEvaluatePrice
    //                it.dynamicMortgageRate = dynamicMortgageRate.setScale(2, BigDecimal.ROUND_HALF_UP)
    //            }
    //            columnarData.dynamicMortgageRate = rateResult
    //        }
    //        else if (city != '-1' && city)
    //        {
    //            def districtList = District.findAllByParentArea(City.findByZoneNumber("${city}"))
    //            def list = new ArrayList()
    //            districtList?.each {
    //                list.add(it.name)
    //            }
    //            def countSql = "select new map(e.count as estateCount,e.zoneNumber as  zoneNumber," + "e.areaName as cityName) from DistrictDataView e where e" + ".areaName in (:districtList)  order by estateCount"
    //            def countResult = DistrictDataView.executeQuery(countSql, ["districtList": list])
    //            countResult.each {
    //                it.allCount = 1000
    //            }
    //            columnarData.estateCount = countResult
    //            //贷款余额和贷款总额
    //            def sql1 = "select new map(e.loansAmount as loansAmountTotal,e.loansBalance as " + "loansBalanceTotal,e.areaName as cityName,e.zoneNumber as  zoneNumber) from DistrictDataView " + "e where e.areaName in (:districtList)  order by loansAmountTotal"
    //            def query = DistrictDataView.executeQuery(sql1, ["districtList": list])
    //            columnarData.loansBalance = query
    //
    //            //每个城市的 动态抵押率=贷款余额/押品价值
    //            def sql2 = "select new map(e.loansBalance as loansBalance, e.loansEvaluateTotalPrice  as  " + "loansEvaluatePrice ,e.areanName as  cityName,e.zoneNumber as  zoneNumber )from " + "Estate e where e.city.areaName in (:districtList)  order by loansEvaluatePrice"
    //            def result = DistrictDataView.executeQuery(sql2, ["districtList": list]).asList()
    //            result.each {
    //                //取小数点
    //                def dynamicMortgageRate = it.loansBalance / it.loansEvaluatePrice
    //                it.dynamicMortgageRate = dynamicMortgageRate.setScale(2, BigDecimal.ROUND_HALF_UP)
    //            }
    //            columnarData.dynamicMortgageRate = result
    //        }
    //        else if (zoneNumber != '-1' && zoneNumber)
    //        {
    //            def districtList = District.findAllByParentArea(City.findByZoneNumber("'${zoneNumber}'"))
    //            def list = new ArrayList()
    //            districtList?.each {
    //                list.add(it.zoneNumber)
    //            }
    //            def countSql = "select new map(e.count as estateCount,e.zoneNumber as  zoneNumber," + "e.areaName as cityName) from DistrictDataView e where e" + "e.zoneNumber in (:districtList)  order by estateCount"
    //            def countResult = DistrictDataView.executeQuery(countSql, ["districtList": list])
    //            countResult.each {
    //                it.allCount = 1000
    //            }
    //            columnarData.estateCount = countResult
    //            //贷款余额和贷款总额
    //            def sql1 = "select new map(e.loansAmount as loansAmountTotal,e.loansBalance as " + "loansBalanceTotal,e.areaName as cityName,e.zoneNumber as  zoneNumber) from DistrictDataView " + "e where e.zoneNumber in (:districtList)  order by loansAmountTotal"
    //            def query = DistrictDataView.executeQuery(sql1, ["districtList": list])
    //            columnarData.loansBalance = query
    //
    //            //每个城市的 动态抵押率=贷款余额/押品价值
    //            def sql2 = "select new map(e.loansBalance as loansBalance, e.loansEvaluateTotalPrice  as  " + "loansEvaluatePrice ,e.areanName as  cityName,e.zoneNumber as  zoneNumber )from " + "Estate e where e.city.zoneNumber in (:districtList)  order by loansEvaluatePrice"
    //            def result = DistrictDataView.executeQuery(sql2, ["districtList": list]).asList()
    //            result.each {
    //                //取小数点
    //                def dynamicMortgageRate = it.loansBalance / it.loansEvaluatePrice
    //                it.dynamicMortgageRate = dynamicMortgageRate.setScale(2, BigDecimal.ROUND_HALF_UP)
    //            }
    //            columnarData.dynamicMortgageRate = result
    //        }
    //        render([dataResult: columnarData] as JSON)
    //    }

    def estateStatistics()
    {
        println new Date()
        def user = User.findById(springSecurityService.principal.id)
        def result = EstateStatistics.find("from EstateStatistics where account.id = ${user?.account?.id}")
        println result
        println new Date()
        render([dataResult: result] as JSON)
    }

    def statisticsView()
    {
        println new Date()
        def db = new groovy.sql.Sql(dataSource)
        def user = User.findById(springSecurityService.principal.id)
        def sql = "SELECT * from wh_estate_statistics where account in " + Account.FilterAccountSql(user)
        def result = db.rows(sql).asList()

        def loansAmount = 0
        def loansBalance = 0
        def loansEvaluatePrice = 0
        def buildArea = 0
        def estateCount = 0
        def monthIncreaseCount = 0

        def resultList = [:]
        result?.each
        {
            loansAmount += it?.loansamount ?: 0
            loansBalance += it?.loansbalance ?: 0
            loansEvaluatePrice += it?.loansevaluateprice ?: 0
            buildArea += it?.buildarea ?: 0
            estateCount += it?.estatecounts ?: 0
            monthIncreaseCount += it?.newcounts ?: 0
        }

        resultList.put("loansAmount", loansAmount)
        resultList.put("loansBalance", loansBalance)
        resultList.put("loansEvaluatePrice", loansEvaluatePrice)
        resultList.put("buildArea", buildArea)
        resultList.put("estateCount", estateCount)
        resultList.put("monthIncreaseCount", monthIncreaseCount)

        println "resultList:" + resultList
        println new Date()
        render([dataResult: resultList] as JSON)
    }
}
